using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.Collections;
using DTO;

namespace DAO
{
    public class BenhNhanDAO
    {
        DataProvider Provider;
        public BenhNhanDAO()
        {
            Provider = new DataProvider();
        }

        public DataTable LayMaBenhNhan()
        {
            
            Provider.Connect();
            string str = "Select MaBenhNhan From BENHNHAN where MaBenhNhan >= all (Select MaBenhNhan From BENHNHAN)";
            DataTable dt = new DataTable();
            OleDbDataAdapter da;
            da = new OleDbDataAdapter(str, Provider.cn);
            da.Fill(dt);
            Provider.Disconnect();
            return dt;
        }

        public DataTable LayBangBenhNhan()
        {
            Provider.Connect();
            string str = "Select * From BENHNHAN";
            DataTable dt = new DataTable();
            OleDbDataAdapter da;
            da = new OleDbDataAdapter(str, Provider.cn);
            da.Fill(dt);
            Provider.Disconnect();
            return dt;

        }
        public DataTable DanhSachKhamBenhTheoNgay(string ngaykham)
        {
            Provider.Connect();//cau truy van nay trong sql chay binh thuong nhung trong nay viet nhu the no khong chiu chay
            string str;
            str = "Select distinct  bn.* From BenhNhan bn, PhieuKham pk  Where bn.MaBenhNhan = pk.MaBN and pk.NgayKham like  '" + ngaykham + "'";
            DataTable dt = new DataTable();
            OleDbDataAdapter da;
            da = new OleDbDataAdapter(str, Provider.cn);
            da.Fill(dt);
            Provider.Disconnect();
            return dt;
        }

        public void ThemBenhNhan(BenhNhanDTO BnDto)
        {
            Provider.Connect();
            string str;
            str = "Insert into BENHNHAN(HoTen, GioiTinh, NgaySinh, DiaChi, MaBenhNhan) values (?, ?, ?, ?, ?)";

            OleDbCommand cmd = new OleDbCommand(str, Provider.cn);
            cmd.Parameters.Add("@HoTen", OleDbType.WChar);
            cmd.Parameters.Add("@GioiTinh", OleDbType.WChar);
            cmd.Parameters.Add("@NgaySinh", OleDbType.Date);
            cmd.Parameters.Add("@DiaChi", OleDbType.WChar);
            cmd.Parameters.Add("@MaBenhNhan", OleDbType.WChar);

            cmd.Parameters["@HoTen"].Value = BnDto.HoTen;
            cmd.Parameters["@GioiTinh"].Value = BnDto.GioiTinh;
            cmd.Parameters["@NgaySinh"].Value = BnDto.NgaySinh;
            cmd.Parameters["@DiaChi"].Value = BnDto.DiaChi;
            cmd.Parameters["@MaBenhNhan"].Value = BnDto.MaBenhNhan;

            cmd.ExecuteNonQuery();
            Provider.Disconnect();

        }


        public void XoaBenhNhan(string maBN)
        {
            // B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            Provider.Connect();
            // B3: Tao chuoi strSQL thao tac CSDL
            string str;
            str = "Delete From BenhNhan Where MaBenhNhan = ?";
            OleDbCommand cmd = new OleDbCommand(str, Provider.cn);
            cmd.Parameters.Add("@MaBenhNhan", OleDbType.Char);
            cmd.Parameters["@MaBenhNhan"].Value = maBN;
            cmd.ExecuteNonQuery();
            // B5: Dong ket noi CSDL
            Provider.Disconnect();
        }
        public void SuaBenhNhan(BenhNhanDTO BnDto)
        {
            Provider.Connect();
            // B3: Tao chuoi strSQL thao tac CSDL
            string str;
            str = "Update BenhNhan Set HoTen = ?,GioiTinh = ?, NgaySinh = ?, DiaChi = ? Where MaBenhNhan = ? ";
            OleDbCommand cmd = new OleDbCommand(str, Provider.cn);

            cmd.Parameters.Add("@HoTen", OleDbType.WChar);
            cmd.Parameters.Add("@GioiTinh", OleDbType.WChar);
            cmd.Parameters.Add("@NgaySinh", OleDbType.Date);
            cmd.Parameters.Add("@DiaChi", OleDbType.WChar);
            cmd.Parameters.Add("@MaBenhNhan", OleDbType.WChar);

            cmd.Parameters["@HoTen"].Value = BnDto.HoTen;
            cmd.Parameters["@GioiTinh"].Value = BnDto.GioiTinh;
            cmd.Parameters["@NgaySinh"].Value = BnDto.NgaySinh;
            cmd.Parameters["@DiaChi"].Value = BnDto.DiaChi;
            cmd.Parameters["@MaBenhNhan"].Value = BnDto.MaBenhNhan;

            cmd.ExecuteNonQuery();
            Provider.Disconnect();
        }


        public DataTable TimKiemTheoMaBN(string maBN)
        {
            Provider.Connect();
            DataTable dt = new DataTable();
            //B3: Tao chuoi strSQL thao tac CSDL
            string str = "Select * From BenhNhan Where MaBenhNhan = '" + maBN + "'";
            //B4: Thuc thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(str, Provider.cn);

            OleDbDataAdapter da;
            da = new OleDbDataAdapter(str, Provider.cn);
            da.Fill(dt);
            Provider.Disconnect();
            return dt;
        }

        public DataTable TimKiemTheoLoaiBenh(string maLoaiBenh)
        {
            Provider.Connect();
            DataTable dt = new DataTable();
            //B3: Tao chuoi strSQL thao tac CSDL
            string str = "Select distinct bn.* From BenhNhan bn, PHIEUKHAM pk, LOAIBENH lb Where bn.MaBenhNhan = pk.MaBN and pk.MaLoaiBenh = lb.MaLoaiBenh and lb.MaLoaiBenh = '" + maLoaiBenh + "'";
            //B4: Thuc thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(str, Provider.cn);

            OleDbDataAdapter da;
            da = new OleDbDataAdapter(str, Provider.cn);
            da.Fill(dt);
            Provider.Disconnect();
            return dt;
        }

        public DataTable TimKiemTheoDiaChi(string diaChi)
        {
            Provider.Connect();
            DataTable dt = new DataTable();
            //B3: Tao chuoi strSQL thao tac CSDL
            string str = "Select * From BenhNhan Where DiaChi like '%" + diaChi + "%'";
            //B4: Thuc thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(str, Provider.cn);

            OleDbDataAdapter da;
            da = new OleDbDataAdapter(str, Provider.cn);
            da.Fill(dt);
            Provider.Disconnect();
            return dt;
        }

        public DataTable TimKiemTheoGioiTinh(string gioiTinh)
        {
            Provider.Connect();
            DataTable dt = new DataTable();
            //B3: Tao chuoi strSQL thao tac CSDL
            string str = "Select * From BenhNhan Where GioiTinh = '" + gioiTinh + "'";
            //B4: Thuc thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(str, Provider.cn);

            OleDbDataAdapter da;
            da = new OleDbDataAdapter(str, Provider.cn);
            da.Fill(dt);
            Provider.Disconnect();
            return dt;
        }

        public DataTable TimKiemTheoTenBenhNhan(string tenBenhNhan)
        {
            Provider.Connect();
            DataTable dt = new DataTable();
            //B3: Tao chuoi strSQL thao tac CSDL
            string str = "Select * From BenhNhan Where HoTen like '%" + tenBenhNhan + "%'";
            //B4: Thuc thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(str, Provider.cn);

            OleDbDataAdapter da;
            da = new OleDbDataAdapter(str, Provider.cn);
            da.Fill(dt);
            Provider.Disconnect();
            return dt;
        }

        public DataTable TimKiemTheoNgaySinh(string ngaySinhTu, string ngaySinhDen)
        {
            Provider.Connect();
            DataTable dt = new DataTable();
            //B3: Tao chuoi strSQL thao tac CSDL
            string str = "Select distinct bn.* From BENHNHAN bn Where bn.NgaySinh between #" + ngaySinhTu + "# and #" + ngaySinhDen + "#";
            //B4: Thuc thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(str, Provider.cn);

            OleDbDataAdapter da;
            da = new OleDbDataAdapter(str, Provider.cn);
            da.Fill(dt);
            Provider.Disconnect();
            return dt;
        }

        public DataTable TimKiemTheoNgayKham(string ngayKhamTu, string ngayKhamDen)
        {
            Provider.Connect();
            DataTable dt = new DataTable();
            //B3: Tao chuoi strSQL thao tac CSDL
            string str = "Select distinct bn.* From BENHNHAN bn, PHIEUKHAM pk Where bn.MaBenhNhan = pk.MaBN and pk.NgayKham between #" + ngayKhamTu + "# and #" + ngayKhamDen + "#";
            //B4: Thuc thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(str, Provider.cn);

            OleDbDataAdapter da;
            da = new OleDbDataAdapter(str, Provider.cn);
            da.Fill(dt);
            Provider.Disconnect();
            return dt;

        }
    }
}
